-- --------------------------------------------------
-- Preparació per utilitzar el llenguatge PL

CREATE PROCEDURAL LANGUAGE plpgsql;



-- --------------------------------------------------
-- --------------DROPS
-- --------------------------------------------------

DROP TABLE IF EXISTS metadades_detalls;
DROP TABLE IF EXISTS metesdublincore;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS usuaris;
DROP TABLE IF EXISTS persones;
DROP TABLE IF EXISTS contenidors;
DROP TABLE IF EXISTS grups;


-- Desactivat per realitzar l'encriptació des de l'aplicatiu
-- DROP TRIGGER IF EXISTS  md5contrasenya ON usuaris;
-- DROP FUNCTION IF EXISTS "modContrasenya"();



DROP SEQUENCE IF EXISTS sequsuaris;
DROP SEQUENCE IF EXISTS seqcontenidors;
DROP SEQUENCE IF EXISTS seqpersones;
DROP SEQUENCE IF EXISTS seqmetesdublincore;
DROP SEQUENCE IF EXISTS seqmetadades_detalls;
DROP SEQUENCE IF EXISTS seqitems;
DROP SEQUENCE IF EXISTS seqgrups;



-- --------------------------------------------------
-- --------------SEQUENCIES
-- --------------------------------------------------

-- Sequence: sequsuaris



CREATE SEQUENCE sequsuaris
  INCREMENT 1
  MINVALUE 0
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE sequsuaris OWNER TO exuocadmin;



-- Sequence: seqpersones



CREATE SEQUENCE seqpersones
  INCREMENT 1
  MINVALUE 0
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE seqpersones OWNER TO exuocadmin;


-- Sequence: seqmetesdublincore



CREATE SEQUENCE seqmetesdublincore
  INCREMENT 1
  MINVALUE 0
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE seqmetesdublincore OWNER TO exuocadmin;




-- Sequence: seqmetadades_detalls



CREATE SEQUENCE seqmetadades_detalls
  INCREMENT 1
  MINVALUE 0
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE seqmetadades_detalls OWNER TO exuocadmin;



-- Sequence: seqitems



CREATE SEQUENCE seqitems
  INCREMENT 1
  MINVALUE 0
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE seqitems OWNER TO exuocadmin;


-- Sequence: seqgrups



CREATE SEQUENCE seqgrups
  INCREMENT 1
  MINVALUE 0
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE seqgrups OWNER TO exuocadmin;



-- Sequence: seqcontenidors



CREATE SEQUENCE seqcontenidors
  INCREMENT 1
  MINVALUE 0
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE seqcontenidors OWNER TO exuocadmin;




-- --------------------------------------------------
-- --------------TAULES
-- --------------------------------------------------





-- Table: contenidors


CREATE TABLE contenidors
(
  id bigint NOT NULL,
  nom character varying(255) NOT NULL DEFAULT 'buit'::character varying,
  datapub date NOT NULL DEFAULT ('now'::text)::date,
  "version" integer DEFAULT 0,
  CONSTRAINT contenidors_pkey PRIMARY KEY (id),
  CONSTRAINT contenidors_nom_key UNIQUE (nom)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE contenidors OWNER TO exuocadmin;


-- Table: grups



CREATE TABLE grups
(
  id integer NOT NULL,
  nom character varying(15) NOT NULL,
  datapub date NOT NULL DEFAULT ('now'::text)::date,
  "version" integer DEFAULT 0,
  CONSTRAINT grups_pkey PRIMARY KEY (id),
  CONSTRAINT grups_nom_key UNIQUE (nom)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE grups OWNER TO exuocadmin;
COMMENT ON TABLE grups IS 'Assignació dels rols als usuaris';


-- Table: items



CREATE TABLE items
(
  id bigint NOT NULL,
  nom character varying(255) NOT NULL DEFAULT 'buit'::character varying,
  datapub date NOT NULL DEFAULT ('now'::text)::date,
  nomarxiu character varying(255) DEFAULT 'buit.txt'::character varying,
  tipus character varying(100) DEFAULT 'pdf'::character varying,
  ubicacio character varying(255) DEFAULT '/srv/diposit/fitxers'::character varying,
  mida integer DEFAULT 0,
  descripcio character varying(255) DEFAULT 'sense descripció'::character varying,
  privat boolean DEFAULT false,

  contenidor_id bigint,
  visites integer DEFAULT 0,
  autor character varying(255) NOT NULL DEFAULT 'EXUOC'::character varying,
  "version" integer DEFAULT 0,
  CONSTRAINT items_pkey PRIMARY KEY (id),
  CONSTRAINT fk5fde7c0daaf72d6 FOREIGN KEY (contenidor_id)
      REFERENCES contenidors (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "U_Items_nom" UNIQUE (nom)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE items OWNER TO exuocadmin;



-- Table: metesdublincore



CREATE TABLE metesdublincore
(
  id bigint NOT NULL,
  nom character varying(255) NOT NULL,
  descripcio character varying(255) DEFAULT 'Desc EXUOC',
  "version" integer DEFAULT 0,
  CONSTRAINT metesdublincore_PKey PRIMARY KEY (id)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE metesdublincore OWNER TO exuocadmin;



-- Table: metadades_detalls



CREATE TABLE metadades_detalls
(
  id bigint NOT NULL DEFAULT nextval('seqmetadades_detalls'::regclass),
  iditem bigint,
  iddublincore bigint,
  valor character varying(255) DEFAULT 'ExUOC',
  "version" integer DEFAULT 0,
  CONSTRAINT "metadades_detall_PKey" PRIMARY KEY (id),
  CONSTRAINT items_FK FOREIGN KEY (iditem)
      REFERENCES items (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT metesdublincore_FK FOREIGN KEY (iddublincore)
      REFERENCES metesdublincore (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE metadades_detalls OWNER TO exuocadmin;



-- Table: persones



CREATE TABLE persones
(
  id bigint NOT NULL,
  nom character varying(30) NOT NULL,
  cognoms character varying(60) DEFAULT 'exuoc',
  adress character varying(100) DEFAULT 'exuoc',
  telf character varying(20) DEFAULT 'exuoc',
  naixament date DEFAULT ('now'::text)::date,
  "version" integer DEFAULT 0,
  CONSTRAINT PK_Persones PRIMARY KEY (id)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE persones OWNER TO exuocadmin;
COMMENT ON TABLE persones IS 'Taula de recollida de dades de les persones donades d''alta';




-- Desactivat per realitzar l'encriptació des de l'aplicatiu

-- Function: "modContrasenya"()


--CREATE OR REPLACE FUNCTION "modContrasenya"()
--  RETURNS trigger AS
--$BODY$
--DECLARE
--BEGIN
--	NEW.contrasenya=md5(NEW.contrasenya);
--	RETURN NEW;
--END;
--$BODY$
--  LANGUAGE 'plpgsql' IMMUTABLE
--  COST 100;
--ALTER FUNCTION "modContrasenya"() OWNER TO exuocadmin;







-- Table: usuaris



CREATE TABLE usuaris
(
  id bigint NOT NULL,
  "login" character(8) NOT NULL,
  contrasenya character varying(32) NOT NULL,
  actiu boolean NOT NULL DEFAULT true,
  grup_id integer NOT NULL DEFAULT 3,
  dataalta date NOT NULL DEFAULT ('now'::text)::date,
  databaixa date ,
  email character varying(60) DEFAULT 'exuoc@exuoc.com',
  CONSTRAINT usuaris_pkey PRIMARY KEY (id),
  CONSTRAINT grups_FK FOREIGN KEY (grup_id)
      REFERENCES grups (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkf814f3321348f28b FOREIGN KEY (id)
      REFERENCES persones (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT Login_U UNIQUE (login)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE usuaris OWNER TO exuocadmin;
COMMENT ON TABLE usuaris IS 'Taula d''usuaris del sistema, administradors, gestors i l''anònim';


-- Desactivat per realitzar l'encriptació des de l'aplicatiu
-- Trigger: md5contrasenya on usuaris

--CREATE TRIGGER md5contrasenya
--  BEFORE INSERT OR UPDATE
--  ON usuaris
--  FOR EACH ROW
--  EXECUTE PROCEDURE "modContrasenya"();

